from tkinter import messagebox
import tkinter as tk,os
from tkinter import *
import pymysql
import array
from pandas import *
import numpy as np
from matplotlib import pyplot as plt
import matplotlib
from matplotlib.backends.backend_tkagg import FigureCanvasTkAgg
from matplotlib.figure import Figure
from matplotlib.pylab import mpl
import time
db = pymysql.connect(host='localhost',user = "root",passwd = "wh416603",db = "roominformation")
cur = db.cursor()
sql = 'select * from history'
cur.execute(sql)
user_x = []
user_y = []
user_z = []
user_w = []
user_a=  []
user_b = []
user_c=  []
user_x1 = []
user_y1 = []
user_z1 = []
user_w1 = []
user_a1 = []
user_b1 = []
user_c1 = []
x1=[]
y1=[]
z1=[]
w1=[]
a1=[]
b1=[]
c1=[]

results = list(cur.fetchall())


for row in results:
    user_x.append(row[0])
    user_y.append(row[1])
    user_z.append(row[2])
    user_w.append(row[3])
    user_a.append(row[4])
    user_b.append(row[5])
    user_c.append(row[6])
global timeoptionlist
timeoptionlist=[]
def creadate(begindate,enddate):
    timeArraynew=time.strptime(begindate,"%Y-%m-%d")
    timeStamp = int(time.mktime(timeArraynew))
    while 1:


        timeArray=time.localtime(timeStamp)
        othertime=time.strftime("%Y-%m-%d",timeArray)
        timeoptionlist.append(othertime)
        timeStamp=timeStamp+86400;
        if timeStamp>int(time.mktime(time.strptime(enddate,"%Y-%m-%d"))):
            break;
creadate('2021-06-13','2021-07-29')
print(timeoptionlist)


class Application(tk.Frame):
    global timeoptionlist
    def __init__(self,master=None):
        self.index=1
        tk.Frame.__init__(self,master)
        self.pack()
        self.createWidgets()
    def createWidgets(self):
        #self.lblImage=tk.Label(self,width=300,height=300)
        #self.lblImage['image']=self.img
        #self.lblImage.pack()
        roomoptionList=['S01','S02','S03','S04','S05','S06','S07','S08','S09','S10','S11','S12','S13','S14','S15','S16','S17','S18','S19','S20']


        self.f=tk.Frame()
        self.f.pack()
        self.roomselect=tk.StringVar()
        self.roomselect.set('S02')
        self.timeselectbegin = tk.StringVar()
        self.timeselectbegin.set('2021-06-13')
        self.timeselectend = tk.StringVar()
        self.timeselectend.set('2021-07-29')

        self.btnPrev=tk.Button(self.f,text='第一页',command=self.prev)
        self.btnPrev.pack(side=tk.LEFT)
        self.btnNext=tk.Button(self.f,text='第二页',command=self.next)
        self.btnNext.pack(side=tk.LEFT)
        #self.btncheckmodel1=tk.Button(self.f,text='按客房号查询',command=self.model1)
        #self.btncheckmodel2 = tk.Button(self.f, text='按时间段查询', command=self.model1)
        self.optionroom = tk.OptionMenu(self.f,self.roomselect, *roomoptionList)
        self.optionroom.pack(side=tk.LEFT)
        self.optionroombegin = tk.OptionMenu(self.f, self.timeselectbegin, *timeoptionlist)
        self.optionroombegin.pack(side=tk.LEFT)
        self.optionroomend = tk.OptionMenu(self.f, self.timeselectend, *timeoptionlist)
        self.optionroomend.pack(side=tk.LEFT)
        self.btnRoomSure=tk.Button(self.f,text='按客房号查询',command=self.model1)
        self.btnRoomSure.pack(side=tk.LEFT)
        self.btnRoomSure2 = tk.Button(self.f, text='按时间段查询', command=self.model2)
        self.btnRoomSure2.pack(side=tk.LEFT)

    def prev(self):
        self.index=1
        self.show(1)
    def next(self):
        self.index=2
        self.show(2)
    def show(self,n):
        #self.index+=n
        if(n==1):
            # 截取每一项数据前六条
            x = user_x[0:10]
            y = user_y[0:10]
            z = user_z[0:10]
            w = user_w[0:10]
            a = user_a[0:10]
            b = user_b[0:10]
            c = user_c[0:10]
            data = np.array([user_x, user_y, user_z, user_w, user_a, user_b, user_c])
            # 因数据过多，如果加入右侧滑动块，展示全部数据，不利于使用，因此展示选择前六条和后两条，中间用省略号表示
            # data1 = np.array([x+['...']+m,y+['...']+n,z+['...']+p,w+['...']+q,a+['...']+d,b+['...']+e,c+['...']+f])
            data1 = np.array([x, y, z, w, a, b, c])
            df = DataFrame(data.T, columns=["HistoryID", "RoomID", "BeginTime", "EndTime", "TotalPrice", "CldentityID",
                                            "Remarks"])
            matplotlib.use('TkAgg')  # 使用组件
            mpl.rcParams['font.sans-serif'] = ['SimHei']  # 组件字体
            root = Tk()  # 构建窗口
            h = plt.figure(figsize=(7.6, 5.2))  # 窗口大小
            j = h.add_subplot(111, frameon=False, xticks=[], yticks=[])  # frameon选择是否显示组建本身的横纵坐标，因为只要显示表格，所以选择False
            j.set_title("历史记录表", loc='center', pad=10, fontsize='xx-large')  # 设置组件相关
            j = df.index[-2] + 1
            i = df.index[-1] + 1  # 截取数据最后的索引，确定数据数目
            table = plt.table(cellText=data1.T, colLabels=df.columns,
                              rowLabels=['1', '2', '3', '4', '5', '6', '7', '8', '9', '10'],
                              colWidths=[0.1] * data.shape[1], loc='upper center', cellLoc='center')
            table.set_fontsize(13)  # 字体大小
            table.scale(1.6, 1.6)  # 每一个小表格的尺寸
            canvas = FigureCanvasTkAgg(h,
                                       master=root)  # 将构建的图表放置在canvas上，该canvas是使用FigureCanvasTkAgg组件构建的，然后设置canvas展示在root窗口
            canvas.draw()
            canvas.get_tk_widget().grid(row=0, columnspan=3)
            root.mainloop()
        if(n==2):
            x = user_x[10:20]
            y = user_y[10:20]
            z = user_z[10:20]
            w = user_w[10:20]
            a = user_a[10:20]
            b = user_b[10:20]
            c = user_c[10:20]
            data = np.array([user_x, user_y, user_z, user_w, user_a, user_b, user_c])
            # 因数据过多，如果加入右侧滑动块，展示全部数据，不利于使用，因此展示选择前六条和后两条，中间用省略号表示
            # data1 = np.array([x+['...']+m,y+['...']+n,z+['...']+p,w+['...']+q,a+['...']+d,b+['...']+e,c+['...']+f])
            data1 = np.array([x, y, z, w, a, b, c])
           # print(data1)
            df = DataFrame(data.T, columns=["HistoryID", "RoomID", "BeginTime", "EndTime", "TotalPrice", "CldentityID",
                                            "Remarks"])
            matplotlib.use('TkAgg')  # 使用组件
            mpl.rcParams['font.sans-serif'] = ['SimHei']  # 组件字体
            root = Tk()  # 构建窗口
            h = plt.figure(figsize=(7.6, 5.2))  # 窗口大小
            j = h.add_subplot(111, frameon=False, xticks=[], yticks=[])  # frameon选择是否显示组建本身的横纵坐标，因为只要显示表格，所以选择False
            j.set_title("历史记录表", loc='center', pad=10, fontsize='xx-large')  # 设置组件相关
            j = df.index[-2] + 1
            i = df.index[-1] + 1  # 截取数据最后的索引，确定数据数目
            table = plt.table(cellText=data1.T, colLabels=df.columns,
                              rowLabels=['11', '12', '13', '14', '15', '16', '17', '18', '19', '20'],
                              colWidths=[0.1] * data.shape[1], loc='upper center', cellLoc='center')
            table.set_fontsize(13)  # 字体大小
            table.scale(1.6, 1.6)  # 每一个小表格的尺寸
            canvas = FigureCanvasTkAgg(h,
                                       master=root)  # 将构建的图表放置在canvas上，该canvas是使用FigureCanvasTkAgg组件构建的，然后设置canvas展示在root窗口
            canvas.draw()
            canvas.get_tk_widget().grid(row=0, columnspan=3)
            root.mainloop()
    def model1(self):
        selectroom=self.roomselect.get()
        name=bytes(selectroom.encode('utf-8'))
        print(selectroom)
        #print(type(name))
        cur.execute("CALL ROOMCHECK('{0}');".format(selectroom))

        res = cur.fetchall()
        for column in res:
            print(column[0])
            #print(column[1])
            if len(user_x1) != 0:
                user_x1.pop()
            user_x1.append(column[0])
            if len(user_y1) != 0:
                user_y1.pop()
            print('asd:',user_x1)
            user_y1.append(column[1])
            if len(user_z1) != 0:
                user_z1.pop()
            user_z1.append(column[2])
            if len(user_w1) != 0:
                user_w1.pop()
            user_w1.append(column[3])
            if len(user_a1) != 0:
                user_a1.pop()
            user_a1.append(column[4])
            if len(user_b1) != 0:
                user_b1.pop()
            user_b1.append(column[5])
            if len(user_c1) != 0:
                user_c1.pop()
            user_c1.append(column[6])

            # user_x1.append(column[0])
            # print('asd:',user_x1)
            # user_y1=column[1]
            # user_z1=column[2]
            # user_w1=column[3]
            # user_a1=column[4]
            # user_b1=column[5]
            # user_c1=column[6]


       # print(res)
       #  x1.append(user_x1[0])
       #  y1.append(user_y1[0])
       #  z1.append(user_z1[0])
       #  w1.append(user_w1[0])
       #  a1.append(user_a1[0])
       #  b1.append(user_b1[0])
       #  c1.append(user_c1[0])
       #  print(x1)
        data = np.array([user_x1, user_y1, user_z1, user_w1, user_a1, user_b1, user_c1])
       #  data = [user_x1, user_y1, user_z1, user_w1, user_a1, user_b1, user_c1]
        # 因数据过多，如果加入右侧滑动块，展示全部数据，不利于使用，因此展示选择前六条和后两条，中间用省略号表示
         # data1 = np.array([x+['...']+m,y+['...']+n,z+['...']+p,w+['...']+q,a+['...']+d,b+['...']+e,c+['...']+f])
        data1 = np.array([x1, y1, z1, w1, a1, b1, c1])
       # print(data1)
        df = DataFrame(data.T, columns=["HistoryID", "RoomID", "BeginTime", "EndTime", "TotalPrice", "CldentityID",
                                            "Remarks"])
        matplotlib.use('TkAgg')  # 使用组件
        mpl.rcParams['font.sans-serif'] = ['SimHei']  # 组件字体
        root = Tk()  # 构建窗口
        rowLabels=[]
        h = plt.figure(figsize=(7.6, 5.2))  # 窗口大小
        j = h.add_subplot(111, frameon=False, xticks=[], yticks=[])  # frameon选择是否显示组建本身的横纵坐标，因为只要显示表格，所以选择False
        j.set_title("按客房号查询结果", loc='center', pad=10, fontsize='xx-large')  # 设置组件相关
        #j = df.index[-2] + 1
        #i = df.index[-1] + 1  # 截取数据最后的索引，确定数据数目
        print('data:\n',type(data1))
        table = plt.table(cellText=data.T, colLabels=df.columns,
                colWidths=[0.1] * data.shape[0], loc='upper center', cellLoc='center')

        table.set_fontsize(13)  # 字体大小
        table.scale(1.6, 1.6)  # 每一个小表格的尺寸
        canvas = FigureCanvasTkAgg(h,
                                   master=root)  # 将构建的图表放置在canvas上，该canvas是使用FigureCanvasTkAgg组件构建的，然后设置canvas展示在root窗口
        canvas.draw()
        canvas.get_tk_widget().grid(row=0, columnspan=3)
        root.mainloop()
    def model2(self):
        user_x2 = []
        user_y2 = []
        user_z2 = []
        user_w2 = []
        user_a2 = []
        user_b2 = []
        user_c2 = []
        x2 = []
        y2 = []
        z2 = []
        w2 = []
        a2 = []
        b2 = []
        c2 = []
        selectimebegin = self.timeselectbegin.get()
        selectimeend = self.timeselectend.get()
        if int(time.mktime(time.strptime(selectimebegin, "%Y-%m-%d")))>int(time.mktime(time.strptime(selectimeend, "%Y-%m-%d"))):
            tk.messagebox.askretrycancel(title='askretrycancel',message='初始日期晚于结束日期，请检查！')
        else:
            print(selectimebegin)
            print(selectimeend)
            # print(type(name))
            cur.execute("CALL TIMECHECK('{0}','{1}');".format(selectimebegin,selectimeend))
            results=list(cur.fetchall())
            print(len(results))
            for row in results:
                if len(user_x2)==len(results):
                    for i in range(len(user_x2)):
                        user_x2.pop()
                user_x2.append(row[0])
                if len(user_y2) == len(results):
                    for i in range(len(user_y2)):
                        user_y2.pop()
                user_y2.append(row[1])
                if len(user_z2) == len(results):
                    for i in range(len(user_z2)):
                        user_z2.pop()
                user_z2.append(row[2])
                if len(user_w2) == len(results):
                    for i in range(len(user_w2)):
                        user_w2.pop()
                user_w2.append(row[3])
                if len(user_a2) == len(results):
                    for i in range(len(user_a2)):
                        user_a2.pop()
                user_a2.append(row[4])
                if len(user_b2) == len(results):
                    for i in range(len(user_b2)):
                        user_b2.pop()
                user_b2.append(row[5])
                if len(user_c2) == len(results):
                    for i in range(len(user_c2)):
                        user_c2.pop()
                user_c2.append(row[6])
            print(len(user_x2))
            x2 = user_x2[0:len(user_x2)]
            y2 = user_y2[0:len(user_x2)]
            z2 = user_z2[0:len(user_x2)]
            w2 = user_w2[0:len(user_x2)]
            a2 = user_a2[0:len(user_x2)]
            b2 = user_b2[0:len(user_x2)]
            c2 = user_c2[0:len(user_x2)]
            data = np.array([user_x2, user_y2, user_z2, user_w2, user_a2, user_b2, user_c2])
            # 因数据过多，如果加入右侧滑动块，展示全部数据，不利于使用，因此展示选择前六条和后两条，中间用省略号表示
            # data1 = np.array([x+['...']+m,y+['...']+n,z+['...']+p,w+['...']+q,a+['...']+d,b+['...']+e,c+['...']+f])
            data1 = np.array([x2, y2, z2, w2, a2, b2, c2])
            df = DataFrame(data.T, columns=["HistoryID", "RoomID", "BeginTime", "EndTime", "TotalPrice", "CldentityID",
                                            "Remarks"])
            matplotlib.use('TkAgg')  # 使用组件
            mpl.rcParams['font.sans-serif'] = ['SimHei']  # 组件字体
            root = Tk()  # 构建窗口
            h = plt.figure(figsize=(7.6, 5.2))  # 窗口大小
            j = h.add_subplot(111, frameon=False, xticks=[], yticks=[])  # frameon选择是否显示组建本身的横纵坐标，因为只要显示表格，所以选择False
            j.set_title("按时间段查询结果", loc='center', pad=10, fontsize='xx-large')  # 设置组件相关
            #j = df.index[-2] + 1
            #i = df.index[-1] + 1  # 截取数据最后的索引，确定数据数目
            table = plt.table(cellText=data1.T, colLabels=df.columns,
                              colWidths=[0.1] * data.shape[0], loc='upper center', cellLoc='center')
            table.set_fontsize(13)  # 字体大小
            table.scale(1.6, 1.6)  # 每一个小表格的尺寸
            canvas = FigureCanvasTkAgg(h,
                                       master=root)  # 将构建的图表放置在canvas上，该canvas是使用FigureCanvasTkAgg组件构建的，然后设置canvas展示在root窗口
            canvas.draw()
            canvas.get_tk_widget().grid(row=0, columnspan=3)
            root.mainloop()

root=tk.Tk()
root.title("数据库查询器")
app=Application(master=root)
app.mainloop()
